In computer related technologies, certain applications such as reports are generated to obtain information on various aspects. For example, in a social networking environment reports may be generated for obtaining information such as the list of users who visited a particular website over a predefined period, number of comments made by users in the last hour, etc. The information presented in the reports is retrieved from a database. Obtaining results from the database every time a report is generated consumes significant computing resources and causes a significant delay.
In at least some embodiments, the queries obtaining the data from one or more tables in the data consume significantly more time if the structure or the design of the one or more tables is not efficient for a particular type of query. The tables may be efficient for a query with a first set of query parameters but not for a query with a second set of query parameters. For example, consider two queries—query Q1 and query Q2 which are looking for specific data in a table A. Consider that query Q1 is searching for data S, T, V, X, Y, and has a “where” clause with some values of X, Y, T, S. Consider that query Q2 is searching for data X, Y and has a “where” clause with some values of X and Y. Further, consider that table A has columns S, T, U, V, W, X, Y and Z, and has “n” number of rows in it.
The query Q1 searching for data S, T, V, X, Y has to scan through “n” rows and then filter the “n” rows based on the values of X, Y, T, S to obtain the query result. Query Q2, which is just searching for X, Y, also has to scan through all the “n” rows before it filters out the scanned rows based on the values of X and Y to obtain the query result. However, the table A is less efficient for query Q2 (than query Q1) since it has a large amount of data than needed for the query. The table A would be more efficient if it had lesser data or only the data, for example, lesser number of columns or only columns X, Y, required by query Q2. A table with a fixed set of columns may not be efficient for different queries with different set of query parameters. Some embodiments have suggested creating indexes in tables to make the search faster. However, the amount of data to be scanned by the different query would still be the same.